#!/usr/bin/python3
import pymysql

# 打开数据库连接
db = pymysql.connect(host='localhost', user='root', password='123456', database='test',port = 3306, charset='utf8') 
# 端口，默认3306，可以不输入,字符集注意不是'utf-8'

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 使用 execute()  方法执行 SQL 查询 
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()

print ("Database version : %s " % data)

# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS test_table")

# 使用预处理语句创建表
create_sql = """CREATE TABLE test_table(id int not null auto_increment,name varchar(20) not null,
    dept_name varchar(20) not null,salary decimal(8,2) DEFAULT null,PRIMARY KEY (id))"""
cursor.execute(create_sql)

# 1、插入语句
insert_sql = """insert into test_table(id,name,dept_name,salary) values(1,'Daniel','math',1600)"""
try:
    cursor.execute(insert_sql)
    # 提交到数据库执行
    db.commit()
except:
    # 如果发生异常则回滚
    db.rollback()
    print('Insert failed')

# 2、批量插入
insert_sql = "insert into test_table(id,name,dept_name,salary) values(%s,%s,%s,%s);"
data_list = [(2,'Alisa','math',1000),(3,'Tom','English',2600),(4,'Jack','Python',3678),(5,'Sam','Chinese',12000)]
try:
    # 使用 executemany 执行批量插入
    cursor.executemany(insert_sql,data_list)
    # 提交到数据库执行
    db.commit()
    print('Insert success')
    # 获取插入的最后一条数据的自增ID
    print(cursor.lastrowid)
except:
    # 如果发生异常则回滚
    db.rollback()
    print('Insert failed')
print('*'*40)

# 3、查询语句
# fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
select_sql = """select * from test_table"""
try:
    cursor.execute(select_sql)
    result = cursor.fetchone()
    print('1、',result)
    print('id = %s,name = %s,dept_name = %s,salary = %s'% (result[0], result[1],result[2],result[3]))
except:
    print('Error: unable to fetch data')
# fetchall(): 接收全部的返回结果行.
# rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。
try:
    cursor.execute(select_sql)
    result = cursor.fetchall()
    print('2、',result)
    for res in result:
        print(res)
except:
    print('Error: unable to fetch data')
# rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。
try:
    cursor.execute(select_sql)
    row_count = cursor.rowcount
    print('3、row_count = ',row_count)
except:
    print('Error: unable to fetch data')
# fetchmany(size=None)：从结果集中获取size个结果，如果size=None，则获取第一个
try:
    cursor.execute(select_sql)
    result_many = cursor.fetchmany(size = None)
    print('4、',result_many)
except:
    print('Error: unable to fetch data')
# 4、更新操作
update_sql = """update test_table set salary = 3600 where id = 2 """
try:
    cursor.execute(update_sql)
    db.commit()
    print('update success')
except:
    db.rollback()

# 5、删除操作
delete_sql = "delete from test_table where salary = %d " % (3600)
try:
    cursor.execute(delete_sql)
    db.commit()
    print('delete success')
except:
    db.rollback()
# 关闭游标
cursor.close()
# 关闭数据库连接
db.close()
